use anyhow::{Result, Context};
use rusqlite::{Connection, params};
use std::path::PathBuf;
use std::fs;
use std::sync::Mutex;
use std::sync::OnceLock;

// 全局数据库连接池
static DB_CONN: OnceLock<Mutex<Connection>> = OnceLock::new();

// 文件记录结构
#[derive(Debug)]
pub struct FileRecord {
    pub file_path: String,  // 文件绝对路径
    pub file_md5: String,   // MD5值
    pub file_size: i64,     // 文件大小（字节）
}

/// 获取数据库连接
fn get_db_conn() -> &'static Mutex<Connection> {
    DB_CONN.get().expect("数据库未初始化")
}

/// 初始化数据库
pub fn init_db() -> Result<()> {
    // 确保数据目录存在
    let data_dir = get_data_dir()?;
    fs::create_dir_all(&data_dir).context("创建数据目录失败")?;

    // 数据库文件路径
    let db_path = data_dir.join("filescanner.db");

    // 创建数据库连接
    let conn = Connection::open(&db_path).context("打开数据库失败")?;

    // 创建表，确保表有正确的结构
    conn.execute(
        "CREATE TABLE IF NOT EXISTS files (
            file_path TEXT PRIMARY KEY,
            file_md5 TEXT NOT NULL,
            file_size INTEGER NOT NULL
        )",
        [],
    ).context("创建表失败")?;

    // 创建索引以加速查询
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_file_md5 ON files(file_md5)",
        [],
    ).context("创建索引失败")?;

    // 存储连接
    DB_CONN.get_or_init(|| Mutex::new(conn));

    Ok(())
}

/// 获取应用数据目录
/// 返回与可执行文件同级的目录
fn get_data_dir() -> Result<PathBuf> {
    // 获取当前可执行文件的路径
    let exe_path = std::env::current_exe().context("无法获取可执行文件路径")?;
    // 获取可执行文件所在的目录
    let exe_dir = exe_path.parent().context("无法获取可执行文件所在目录")?;
    Ok(exe_dir.to_path_buf())
}

/// 检查文件是否已在数据库中
pub fn is_file_in_db(file_path: &str) -> Result<Option<FileRecord>> {
    let conn = get_db_conn().lock().unwrap();

    let mut stmt = conn.prepare(
        "SELECT file_path, file_md5, file_size FROM files WHERE file_path = ?1"
    ).context("准备SQL语句失败")?;

    let records = stmt.query_map(params![file_path], |row| {
        Ok(FileRecord {
            file_path: row.get(0)?,
            file_md5: row.get(1)?,
            file_size: row.get(2)?,
        })
    }).context("查询数据失败")?;

    let mut record = None;
    for r in records {
        record = Some(r?);
        break;
    }

    Ok(record)
}

/// 保存文件记录到数据库
pub fn save_file(file_path: &str, file_md5: &str, file_size: i64) -> Result<()> {
    let conn = get_db_conn().lock().unwrap();

    conn.execute(
        "INSERT OR REPLACE INTO files (file_path, file_md5, file_size) VALUES (?1, ?2, ?3)",
        params![file_path, file_md5, file_size],
    ).context("保存文件记录失败")?;

    Ok(())
}

/// 删除数据库中的文件记录
pub fn delete_file(file_path: &str) -> Result<()> {
    let conn = get_db_conn().lock().unwrap();

    conn.execute(
        "DELETE FROM files WHERE file_path = ?1",
        params![file_path],
    ).context("删除文件记录失败")?;

    Ok(())
}

/// 获取所有文件记录
pub fn get_all_files() -> Result<Vec<FileRecord>> {
    let conn = get_db_conn().lock().unwrap();

    let mut stmt = conn.prepare(
        "SELECT file_path, file_md5, file_size FROM files"
    ).context("准备SQL语句失败")?;

    let records = stmt.query_map([], |row| {
        Ok(FileRecord {
            file_path: row.get(0)?,
            file_md5: row.get(1)?,
            file_size: row.get(2)?,
        })
    }).context("查询数据失败")?;

    let mut result = Vec::new();
    for record in records {
        result.push(record?);
    }

    Ok(result)
}

/// 获取具有重复MD5值的文件记录
pub fn get_duplicate_files() -> Result<Vec<Vec<FileRecord>>> {
    let conn = get_db_conn().lock().unwrap();

    // 首先查找重复的MD5值
    let mut stmt = conn.prepare(
        "SELECT file_md5 FROM files GROUP BY file_md5 HAVING COUNT(*) > 1"
    ).context("准备SQL语句失败")?;

    let duplicate_md5s = stmt.query_map([], |row| {
        let md5: String = row.get(0)?;
        Ok(md5)
    }).context("查询数据失败")?;

    let mut result = Vec::new();

    // 对于每个重复的MD5值，查询对应的文件记录并按文件大小从大到小排序
    for md5_result in duplicate_md5s {
        let md5 = md5_result?;

        let mut stmt = conn.prepare(
            "SELECT file_path, file_md5, file_size FROM files
             WHERE file_md5 = ?1
             ORDER BY file_size DESC"
        ).context("准备SQL语句失败")?;

        let files = stmt.query_map(params![md5], |row| {
            Ok(FileRecord {
                file_path: row.get(0)?,
                file_md5: row.get(1)?,
                file_size: row.get(2)?,
            })
        }).context("查询数据失败")?;

        let mut files_with_same_md5 = Vec::new();
        for file in files {
            files_with_same_md5.push(file?);
        }

        if !files_with_same_md5.is_empty() {
            result.push(files_with_same_md5);
        }
    }

    Ok(result)
}
